const Router = require('koa-router');
const router = new Router();
const {db, QueryTypes} = require("../../models/db");
const tool = require("../../tools/tool"); const tools = require("../../tools/tool");
 
// 重算所有商品 变动明细
router.post('/sets/initInventory', async ctx => {
    let ut = tools.getTime()
    let tenantId =  4 //ctx.user.tenantId;
    let loop = 0
    try {
        await db.models['inventoryDetail'].destroy({
            where: {
                tenantId
            }
        }, )
        await db.models['inventory'].destroy({
            where: {
                tenantId
            }
        }, )
        // 所有期初+所有审核的业务数据
        await db.transaction(async () => {
            let initSql = ` 
            select * from 
                         (
                             select 1 as isInit,1 as seq, a.tenantId,a.locationId,a.invId,'' billNo,0 relId,'1970-01-01' as billDate,N'期初' as transTypeName,0 transType,isnull(ig.qty, 0) as lastCount,
                                    case when ig.amount is not null then (case when isnull(ig.qty,0)=0 then 0 else ig.amount*1.0/ig.qty end) else isnull(a.price1*1.0/a.spec,0) end lastPrice,
                                    isnull(ig.amount,0) lastMoney,
                                    case when ig.unit is null then (case when a.unit2 is null then a.unit1 else a.unit2 end) else ig.unit end as unit,
                                    1 calNum,a.spec,a.unit1,a.unit2,0 inCount,0 inPrice,0 inMoney,0 outCount,0 outPrice,0 outMoney,0 costPrice,0 costMoney,0 as iid, '${ut}' as ctime
                             from (
                                      select a.tenantId, a.id invId,a.spec,a.unit1,a.unit2,a.price1, b.id locationId,b.name locationName 
                                      from v_goods a inner join storage b on a.tenantId=b.tenantId and a.tenantId=${tenantId} 
                                  ) a  left join init_goods ig on a.tenantId=ig.tenantId and a.locationId=ig.locationId and a.invId=ig.invId
                             union all
                             select 0 as isInit, 2 as seq, a.tenantId, a.locationId,a.invId,a.billNo,a.id,a.billDate,a.transTypeName,a.transType,0 lastCount ,0 lastPrice ,0 lastMoney,
                                    case when isnull(c.unit2,'') = '' then c.unit1 else c.unit2 end,
                                    c.spec,1 as calNum,c.unit1,c.unit2,
                                    case when a.billType in('PUR', 'OI') then a.qtyReal*a.calNum else 0 end as inCount,
                                    case when a.billType in('PUR', 'OI') then iif(a.qtyReal=0,0,a.amountReal*1.0/(a.qtyReal*a.qtyReal))  else 0 end as inPrice,
                                    case when a.billType in('PUR', 'OI') then a.amountReal else 0 end as inMoney,
                                    case when a.billType in('SALE', 'OO') then a.qtyReal*a.calNum else 0 end as outCount,
                                    case when a.billType in('SALE', 'OO') then iif(a.qtyReal=0,0,a.amountReal*1.0/(a.qtyReal*a.calNum))  else 0 end as outPrice,
                                    case when a.billType in('SALE', 'OO') then a.amountReal else 0 end as outMoney,
                                    0 as costPrice,0 as costMoney,a.iid,'${ut}' as ctime
                             from v_invoice_info_detail_accounted as a 
                                      inner join v_goods as c on a.invId=c.id
                             where a.tenantId=${tenantId}  
                         ) a   order by tenantId,locationId,invId,billDate,transType 
        `
            let list = await db.query(initSql, {type: QueryTypes.SELECT})
            for (let i = 0; i < list.length; i++) {
                let row = list[i]
                let transType = row.transType
                if(row.isInit === 0) {
                    let last = list[i-1]
                    list[i].seq = tools.add(last.seq, 1)
                    if(transType === 150501 || transType === 150706 || transType === 150502) {
                        list[i].lastCount = tools.add(last.lastCount, row.inCount)
                        list[i].lastMoney = tools.xe.round(tools.add(last.lastMoney, row.inMoney),2)
                        list[i].lastPrice = tools.xe.toNumber(list[i].lastCount) === 0 ? last.lastPrice : (tools.divide(list[i].lastMoney,list[i].lastCount))
                    }
                    if(transType === 150601 || transType === 150602 || transType === 150603 || transType === 150806) {
                        list[i].costPrice = last.lastPrice
                        list[i].costMoney = tools.xe.round(tools.multiply(row.outCount, list[i].costPrice),2)
                        list[i].lastCount = tools.minus(last.lastCount, row.outCount)
                        list[i].lastMoney = tools.xe.round(tools.minus(last.lastMoney, list[i].costMoney),2)
                        let p = tools.xe.toNumber(list[i].lastCount) === 0
                            ?(tools.xe.toNumber(last.lastPrice)):(tools.divide(tools.xe.toNumber(list[i].lastMoney), tools.xe.toNumber(list[i].lastCount)))
                        list[i].lastPrice = p ? p : 0
                    }
                }
            }
            let uplist = []
            for (let i = 0; i < list.length; i++) {
                uplist.push(list[i])
            }
            let size = 1000
            let page = Math.ceil(uplist.length / size)
            for (let i = 0; i < page; i++) {
                let start = i * size
                let end = (start + size) > uplist.length ? uplist.length : (start + size)
                let arr = uplist.slice(start, end)
                await db.models['inventoryDetail'].bulkCreate(arr)
            }
            let updateInv = `
            with bills as
            (
                select a.tenantId, a.locationId, a.invId, a.billDate, a.lastCount, a.lastPrice, a.lastMoney
                from inventory_detail a
                inner join
                (
                    select max(seq) as seq, tenantId, locationId, invId from inventory_detail a  where a.tenantId = ${tenantId}
                    GROUP BY a.tenantId, a.locationId, a.invId
                ) b on a.tenantId = b.tenantId and a.locationId = b.locationId and a.invId = b.invId and a.seq = b.seq
            )
            merge into inventory a using bills  b
            on a.tenantId=b.tenantId and a.locationId=b.locationId and a.invId=b.invId
            when matched then update set a.lastMoney=b.lastMoney,a.lastPrice=b.lastPrice,a.lastCount=b.lastCount
            when not matched then insert (tenantId,locationId,invId,lastCount,lastPrice,lastMoney) values
            (b.tenantId,b.locationId,b.invId,b.lastCount,b.lastPrice,b.lastMoney);
        `
            await db.query(updateInv, {type: QueryTypes.UPDATE})
        })
        return ctx.suc('更新成功')
    }
    catch (e) {
        return ctx.err(e)
    }
})

module.exports = router;